<html>
<script>

// Open a Web SQL database.
var g_db = null;
if (typeof window.openDatabase == "undefined") {
  document.write("Error: Web SQL databases are not supported.");
}
try {
  g_db = openDatabase("test", "1.0", "test database", 1024 * 1024);
} catch(err) {
  document.write("Error: cannot open database.");
}

// Creates a table named "table1" with one text column named "data".
async function createTable() {
  if (!g_db)
    return;
  try {
    await new Promise((resolve, reject) => {
      g_db.transaction(
        function(tx) {
          tx.executeSql("CREATE TABLE table1 (data TEXT)");
        },
        reject, resolve);
    });
    return "done";
  } catch (error) {
    return error;
  }
}

// Inserts a record into the database.
async function insertRecord(text) {
  try {
    await new Promise((resolve, reject) => {
      g_db.transaction(
      function(tx) {
        tx.executeSql("INSERT INTO table1 VALUES (?)", [text]);
      }, reject, resolve);
    });
    return "done";
  } catch (error) {
    return error;
  }
}


// Updates a record at the given index with the given text. The indices are
// 0-based and are ordered from oldest record, to newest record.
async function updateRecord(index, text) {
  var didUpdate = false;
  try {
    const rowId = await findId(index);
    return new Promise((resolve, reject) => {
      g_db.transaction(
        function(tx) {
          tx.executeSql(
              "UPDATE table1 SET data=? WHERE ROWID=?",
              [text, rowId],
              function(tx, result) {
                if (result.rowsAffected == 1)
                  didUpdate = true;
                else if (result.rowsAffected == 0)
                  reject("could not update index: " + index);
                else
                  reject("multiple rows with index: " + index);
              });
        },
        function(error) {
          reject("update error: " + error);
        },
        function() {
          if (didUpdate)
            resolve("done");
        });
    });
  } catch (error) {
    return error;
  }
}

// Deletes a record at the given index.
async function deleteRecord(index) {
  try {
    const rowId = await findId(index);
    await new Promise((resolve, reject) => {
      g_db.transaction(
        function(tx) {
          tx.executeSql("DELETE FROM table1 WHERE ROWID=?", [rowId]);
        },
        function(error) {
          reject("delete error: " + error);
        }, resolve);
      });
    return "done";
  } catch (error) {
    return error;
  }
}

// Gets all the records in the database, ordered by their age.
async function getRecords() {
  try {
    const result = await new Promise((resolve, reject) => {
      g_db.readTransaction(function(tx) {
      tx.executeSql(
          "SELECT data FROM table1 ORDER BY ROWID",
          [],
          function(tx, result) {
            resolve(result);
          },
          function(tx, error) {
            reject("getRecords error: " + error);
          });
      });
    });
    let items = "";
    for (var i = 0; i < result.rows.length; i++) {
      if (items != "")
        items += ", ";
      items += result.rows.item(i).data;
    }
    return items;
  } catch (error) {
    return error;
  }
}

// Helper function that finds the ID for a record based on a given index.
async function findId(index) {
  return new Promise((resolve, reject) => {
    g_db.readTransaction(function(tx) {
      // |ROWID| is a special sqlite column. It is unique and is incremented
      // automatically when a new record is created.
      // |LIMIT| is a nonstandard clause supported by sqlite that lets us pick
      // rows from the database by index. E.g., LIMIT 2,10 will give us 10 records
      // starting at offset 2.
      tx.executeSql(
        "SELECT ROWID AS id FROM table1 ORDER BY ROWID LIMIT ?,1",
        [index],
        function(tx, result) {
          if (result.rows.length >= 1)
            resolve(result.rows.item(0).id);
          else
            reject("could not find row with index: " + index);
        },
        function(tx, error) {
          reject("findId error: " + error);
        });
    });
  });
}

</script>

<body>
This page is used for testing Web SQL databases.
</body>
</html>
